package com.customization.huangwm.utils;

import java.util.HashMap;
import java.util.Map;
import java.util.UUID;

import jxl.read.biff.Record;
import org.apache.axis.components.logger.LogFactory;
import org.apache.commons.logging.Log;
import org.apache.log4j.lf5.viewer.LogTable;

import weaver.conn.RecordSet;
import weaver.formmode.setup.ModeRightInfo;
import weaver.general.Util;

/**
 * @author Huangwm
 * @description 使用的后台数据库操作方法
 */
public class DBTools {

    private LogTool log = new LogTool("/log/huangwm/utils/DBTools", false);

    /**
     * 根据用户id获取部门id
     * @param userid
     * @return departmentid
     */
    public String getDepid(String userid) {
        if (userid.equals("") || userid.equals("1")) {
            return "0";
        }
        RecordSet rs = new RecordSet();
        rs.execute("select departmentid from hrmresource where id=" + userid);
        log.writeLog("DBTools getDepid---> select departmentid from hrmresource where id=" + userid);
        if (rs.next()) {
            return rs.getString("departmentid");
        } else {
            return "0";
        }
    }

    /**
     * 根据用户id获取部门名称
     * @param userid
     * @return departmentname
     */
    public String getDepName(String userid) {
        if (userid.equals("") || userid.equals("1")) {
            return "0";
        }
        RecordSet rs = new RecordSet();
        rs.execute("select departmentname from HrmDepartment where id = (select subcompanyid1 from HrmResource where id=" + userid + ")");
        log.writeLog("DBTools getDepName---> select departmentname from HrmDepartment where id = (select subcompanyid1 from HrmResource where id=" + userid + ")");
        if (rs.next()) {
            return rs.getString("departmentname");
        } else {
            return "null";
        }
    }

    /**
     * 根据用户id获取部门名称
     * @param userid
     * @return departmentname
     */
    public String getDepCode(String userid) {
        if (userid.equals("") || userid.equals("1")) {
            return "0";
        }
        RecordSet rs = new RecordSet();
        rs.execute("select departmentcode from HrmDepartment where id = (select subcompanyid1 from HrmResource where id=" + userid + ")");
        log.writeLog("DBTools departmentcode---> select departmentcode from HrmDepartment where id = (select subcompanyid1 from HrmResource where id=" + userid + ")");
        if (rs.next()) {
            return rs.getString("departmentcode");
        } else {
            return "null";
        }
    }

    /**
     * 根据部门id获取部门名称
     * @param depid
     * @return departmentname
     */
    public String getDepNameById(String depid) {
        if (depid.equals("") || depid.equals("1")) {
            return "0";
        }
        RecordSet rs = new RecordSet();
        rs.execute("select departmentname from HrmDepartment where id =" + depid);
        log.writeLog("DBTools getDepNameById--> select departmentname from HrmDepartment where id =" + depid);
        if (rs.next()) {
            return rs.getString("departmentname");
        } else {
            return "null";
        }
    }

    /**
     * 根据用户id获取分部id
     * @param userid
     * @return subcompanyid1
     */
    public String getSubid(String userid) {
        if (userid.equals("") || userid.equals("1")) {
            return "0";
        }
        RecordSet rs = new RecordSet();
        rs.execute("select subcompanyid1 from hrmresource where id=" + userid);
        log.writeLog("DBTools getSubid---> select subcompanyid1 from hrmresource where id=" + userid);
        if (rs.next()) {
            return rs.getString("subcompanyid1");
        } else {
            return "0";
        }
    }

    /**
     * 根据用户id获取分部名称
     * @param userid
     * @return subcompanyname
     */
    public String getSubName(String userid) {
        if (userid.equals("") || userid.equals("1")) {
            return "0";
        }
        RecordSet rs = new RecordSet();
        rs.execute("select subcompanyname from HrmSubCompany where id = (select subcompanyid1 from HrmResource where id=" + userid + ")");
        log.writeLog("DBTools getSubName---> select subcompanyname from HrmSubCompany where id = (select subcompanyid1 from HrmResource where id=" + userid + ")");
        if (rs.next()) {
            return rs.getString("subcompanyname");
        } else {
            return "null";
        }
    }

    /**
     * 根据部门id获取分部名称
     * @param subid
     * @return subcompanyname
     */
    public String getSubNameById(String subid) {
        if (subid.equals("") || subid.equals("1")) {
            return "0";
        }
        RecordSet rs = new RecordSet();
        rs.execute("select subcompanyname from HrmSubCompany where id = " + subid);
        log.writeLog("DBTools getSubNameById---> select subcompanyname from HrmSubCompany where id = " + subid);
        if (rs.next()) {
            return rs.getString("subcompanyname");
        } else {
            return "0";
        }
    }

    /**
     * 根据用户id获取姓名
     * @param userid
     * @return lastname
     */
    public String getLastname(String userid) {
        if (userid.equals("1")) {
            return "系统管理员";
        }
        RecordSet rs = new RecordSet();
        rs.execute("select lastname from hrmresource where id=" + userid);
        log.writeLog("DBTools getLastname---> select lastname from hrmresource where id = " + userid);
        if (rs.next()) {
            return rs.getString("lastname");
        } else {
            return "null";
        }
    }

    /**
     * 根据用户id获取姓名
     * @param userid
     * @return lastname
     */
    public String getWorkCode(String userid) {
        if (userid.equals("1")) {
            return "系统管理员";
        }
        RecordSet rs = new RecordSet();
        rs.execute("select workcode from hrmresource where id=" + userid);
        log.writeLog("DBTools getWorkCode---> select workcode from hrmresource where id = " + userid);
        if (rs.next()) {
            return rs.getString("workcode");
        } else {
            return "未获取";
        }
    }

    /**
     * 根据名获取用户id
     * @param name
     * @return userid
     */
    public String getUserid(String name) {
        RecordSet rs = new RecordSet();
        rs.execute("select id from hrmresource where lastname = '%" + name + "%'");
        log.writeLog("DBTools getUserid---> select id from hrmresource where lastname = '%" + name + "%'");
        if (rs.next()) {
            return rs.getString("id");
        } else {
            return "0";
        }
    }

    /**
     * 根据工号获取用户id
     * @param code
     * @return userid
     */
    public String getUseridByCode(String code) {
        RecordSet rs = new RecordSet();
        rs.execute("select id from hrmresource where workcode = '" + code + "'");
        log.writeLog("DBTools getUseridByCode---> select id from hrmresource where workcode = '" + code + "'");
        if (rs.next()) {
            return rs.getString("id");
        } else {
            return "0";
        }
    }
    /**
     * 根据工号获取用户id
     * @param loginid
     * @return userid
     */
    public String getUseridByLoginid(String loginid) {
        RecordSet rs = new RecordSet();
        rs.execute("select id from hrmresource where loginid = '" + loginid + "'");
        log.writeLog("DBTools getUseridByCode---> select id from hrmresource where workcode = '" + loginid + "'");
        if (rs.next()) {
            return rs.getString("id");
        } else {
            return "0";
        }
    }

    /**
     * 根据用户Id和获取的字段查询人员表获取字段值
     * @param userid
     * @param field
     * @return
     */
    public String getUserValue(String userid, String field) {
        if (userid.isEmpty() || field.isEmpty()) return "";
        RecordSet rs = new RecordSet();
        String sql = "select " + field + " from HrmResource where id=" + userid;
        rs.execute(sql);
        log.writeLog("getUserValue>" + sql);
        if (rs.next()) {
            return rs.getString(field);
        }
        return "";
    }

    /**
     * 生成UUID
     * @return
     */
    public String getUUID() {
        String uuid = UUID.randomUUID().toString().toLowerCase();
        return uuid;
    }

    /**
     * 根据建模表名和uuid查找对应的数据id
     * @param tablename
     * @param uuid
     * @return
     */
    public String getBillid(String tablename, String uuid) {
        String id = "";
        RecordSet rs = new RecordSet();
        String sql = "select * from " + tablename + " where uuid='" + uuid + "'";
        rs.execute(sql);
        log.writeLog("DBTools getBillid---> " + sql);
        if (rs.next()) {
            return rs.getString("id");
        }
        return id;
    }

    /**
     * 表单建模权限重构方法，重构后才能在台账里查看到数据
     * @param creater    创建人
     * @param formmodeid 模块id
     * @param billid     数据id,即对应uf表里的id
     */
    public void refreshRight(int creater, int formmodeid, int billid) {
        try {
            ModeRightInfo modeRightInfo = new ModeRightInfo();
            //console.info("当前重构数据：creater=" + creater + "  formmodeid=" + formmodeid + "  billid=" + billid);
            modeRightInfo.setNewRight(true);
            modeRightInfo.editModeDataShare(creater, formmodeid, billid);
            modeRightInfo.addDocShare(creater, formmodeid, billid);
        } catch (Exception e) {
            log.writeLog("DBTools refreshRight建模权限重构代码报错：creater=" + creater + "  formmodeid=" + formmodeid + "  billid=" + billid);
            log.writeLog(e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * 获取流程创建时间
     * @param requestid
     * @return
     */
    public String GetWorkflowCreatetime(String requestid) {
        RecordSet rs = new RecordSet();
        String WorkflowCreatetime = "";
        if (!"".equals(requestid)) {
            rs = new RecordSet();
            String sql = "select createdate,createtime from workflow_requestbase where requestid=" + requestid;
            rs.execute(sql);
            if (rs.next())
                WorkflowCreatetime = Util.null2String(rs.getString("createdate") + " " + rs.getString("createtime"));
        }
        return WorkflowCreatetime;
    }

    /**
     * 根据requestid获取对应表单tablename
     * @param requestid
     * @return
     */
    public String getBillTableName(String requestid) {
        String tablename = "";
        if (!"".equals(requestid)) {
            RecordSet rs = new RecordSet();
            String sql = " select wb2.tablename from workflow_requestbase wr left join workflow_base wb1 on wb1.id=wr.workflowid" +
                    "\n left join workflow_bill wb2 on wb1.formid=wb2.id where requestid=" + requestid;
            rs.execute(sql);
            if (rs.next()) tablename = rs.getString("tablename");
        }
        return tablename;
    }

    //-----------------SW003---------------------

    public String getYyDepCode(String userid){
        if(userid.isEmpty()) return "";
        String code = "";
        String sql = "select * from cus_fielddata where scope='HrmCustomFieldByInfoType' and id="+userid;
        RecordSet rs = new RecordSet();
        rs.execute(sql);
        if (rs.next()) {
            code = rs.getString("field9");
        }
        return code;
    }
    /**
     * 根据名称检查物料单位是否存在
     * @param rs
     * @param name
     * @return
     */
    public String getUnitCodeByName(RecordSet rs, String name) {
        if (name.isEmpty()) return "單位[" + name + "]不存在";
        String code = "單位[" + name + "]不存在";
        rs.execute("select unit_code from uf_003_unit where unit_name='" + name + "'");
        if (rs.next()) {
            code = rs.getString("unit_code");
        }
        return code;
    }

    /**
     * 根据名称检查物料单位是否存在
     * @param rs
     * @param id
     * @return
     */
    public String getUnitCodeById(RecordSet rs, String id) {
        if (id.isEmpty()) return "單位[" + id + "]不存在";
        String code = "單位[" + id + "]不存在";
        rs.execute("select unit_code from uf_003_unit where id='" + id + "'");
        if (rs.next()) {
            code = rs.getString("unit_code");
        }
        return code;
    }

    /**
     * 根据名称检查物料种类是否存在
     * @param rs
     * @param name
     * @return
     */
    public String getTypeCodeByName(RecordSet rs, String name) {
        if (name.isEmpty()) return "物料種類[" + name + "]不存在";
        String code = "物料種類[" + name + "]不存在";
        try {
            rs.execute("select type_code from uf_003_Type where type_name='" + name + "'");
            if (rs.next()) {
                code = rs.getString("type_code");
            }
        } catch (Exception e) {
            return code + ">" + e.getMessage();
        }
        return code;
    }
    /**
     * 根据EAP物料种类name獲取用友物料种类
     * @param rs
     * @param name
     * @return
     */
    public String getEapTypeNameCompareYY(RecordSet rs, String name) {
        if (name.isEmpty()) return "EAP物料種類[" + name + "]不存在";
        String code = "";
        rs.execute("select eap.yytype,yy.type_code from uf_003_typeForEap eap\n" +
                "left join uf_003_type yy\n" +
                "on eap.yytype=yy.id  where eap.name='"+name+"'");
        if (rs.next()) {
            code = rs.getString("type_code");
        }
        return code;
    }

    /**
     * 根据EAP物料种类id獲取用友物料种类
     * @param rs
     * @param id
     * @return
     */
    public String getEapTypeIdCompareYY(RecordSet rs, String id) {
        if (id.isEmpty()) return "EAP物料種類[" + id + "]不存在";
        String code = "";
        rs.execute("select eap.yytype,yy.type_code from uf_003_typeForEap eap\n" +
                "left join uf_003_type yy\n" +
                "on eap.yytype=yy.id  where eap.id='"+id+"'");
        if (rs.next()) {
            code = rs.getString("type_code");
        }
        return code;
    }

    /**
     * 根据EAP物料种类id獲取OA数据库中的用友物料种类id
     * @param rs
     * @param id
     * @return
     */
    public String getEapTypeIdCompareYYID(RecordSet rs, String id) {
        if (id.isEmpty()) return "0";
        String yyid = "";
        rs.execute("select eap.yytype,yy.type_code from uf_003_typeForEap eap\n" +
                "left join uf_003_type yy\n" +
                "on eap.yytype=yy.id  where eap.id='"+id+"'");
        if (rs.next()) {
            yyid = rs.getString("yytype");
        }
        return yyid;
    }

    /**
     * 根据檢查管理部門是否存在
     * @param rs
     * @param name
     * @return
     */
    public String exisManagerDep(RecordSet rs, String name) {
        if (name.isEmpty()) return "管理部門[" + name + "]不存在";
        String code = "管理部門[" + name + "]不存在";
        rs.execute("select id from hrmdepartment where departmentname='" + name + "'");
        if (rs.next()) {
            code = rs.getString("id");
        }
        return code;
    }

    /**
     * 根据物料id取物料編號
     * @param rs
     * @param id
     * @return
     */
    public String getAssetCode(RecordSet rs, String id) {
        String code = "";
        rs.execute("select assetcode from uf_003_AssteInfo where id='" + id + "'");
        if (rs.next()) {
            code = rs.getString("assetcode");
        }
        return code;
    }

    /**
     * 根据物料code获取物料名称
     * @param rs
     * @param code
     * @return
     */
    public String getAssetNameByCode(RecordSet rs, String code) {
        String name = "";
        rs.execute("select assetname from uf_003_AssteInfo where assetcode='" + code + "'");
        if (rs.next()) {
            name = rs.getString("assetname");
        }
        return name;
    }
    /**
     * 根据物料code获取物料名称
     * @param rs
     * @param code
     * @return
     */
    public String getAssetidByCode(RecordSet rs, String code) {
        String id = "";
        rs.execute("select id from uf_003_AssteInfo where assetcode='" + code + "'");
        if (rs.next()) {
            id = rs.getString("id");
        }
        return id;
    }

    /**
     * 根据EAP物料种类id獲取用友物料种类
     * @param rs
     * @param id
     * @return
     */
    public String getTypeCode2(RecordSet rs, String id) {
        String code = "";
        rs.execute("select eap.yytype,yy.type_code from uf_003_typeForEap eap\n" +
                "left join uf_003_type yy\n" +
                "on eap.yytype=yy.id  where eap.id="+id);
        if (rs.next()) {
            code = rs.getString("type_code");
        }
        return code;
    }

    /**
     * 根据物料code获取物料字段信息
     * @param rs
     * @param code
     * @param field
     * @return
     */
    public String getAssetInfoByCode(RecordSet rs, String code,String field) {
        String info = "";
        rs.execute("select "+field+" from uf_003_AssteInfo where assetcode='" + code + "'");
        if (rs.next()) {
            info = rs.getString(field);
        }
        return info;
    }

    /**
     * 根据物料code获取物料字段信息
     * @param rs
     * @param code
     * @return Map<String,String>
     */
    public Map<String,String> getAssetAllFieldoByCode(RecordSet rs, String code) {
        Map<String,String> data = new HashMap<>();
        rs.execute("select * from uf_003_AssteInfo where assetcode='" + code + "'");
        if (rs.next()) {
            String[] cloumns = rs.getColumnName();
            for(int i=0;i<cloumns.length;i++){
                data.put(cloumns[i].toLowerCase(),rs.getString(cloumns[i]));
            }
        }
        return data;
    }

     /* 根据物料id获取物料字段信息
     * @param rs
     * @param code
     * @return Map<String,String>
     */
    public Map<String,String> getAssetAllFieldoByID(RecordSet rs, String id) {
        Map<String,String> data = new HashMap<>();
        rs.execute("select * from uf_003_AssteInfo where id='" + id + "'");
        if (rs.next()) {
            String[] cloumns = rs.getColumnName();
            for(int i=0;i<cloumns.length;i++){
                data.put(cloumns[i].toLowerCase(),rs.getString(cloumns[i]));
            }
        }
        return data;
    }

    /**
     * 检查EAP仓库是否存在
     * @param rs
     * @param name
     * @return
     */
    public String exisWarehouse(RecordSet rs, String name) {
        if (name.isEmpty()) return "EAP倉庫[" + name + "]不存在";
        String id = "EAP倉庫[" + name + "]不存在";
        rs.execute("select id from uf_003_yyWarehouse where Warehouse='" + name + "'");
        if (rs.next()) {
            id = rs.getString("id");
        }
        return id;
    }

    /**
     * 根據管理部門獲取對應的用友倉庫key
     * @param manageDep
     * @return
     */
    public String getWarehouse(String manageDep){
        RecordSet rs = new RecordSet();
        String sql = "select warehousekey from uf_003_depToWH where managedep="+manageDep;
        log.writeLog("getWarehouse>"+sql);
        rs.execute(sql);
        if(rs.next()){
            return rs.getString("warehousekey");
        }
        return "02";
    }

    /**
     * 根据资产id获取对应的物料id
     * @param mainAssetCode
     * @return
     */
    public String getAssetid(String mainAssetCode){
        RecordSet rs = new RecordSet();
        String sql = "select relationCode from uf_003_AssetInfo2 where id="+mainAssetCode;
        rs.execute(sql);
        rs.next();
        return rs.getString("relationCode");
    }

    //------------------------SW003----------------------------
    public String getCurrentoperatorFromNodeName(String table,String requestid, String nodeName) {
        String Currentoperator = "";
        String nodeSql = "select userid from (select rownum as rm, operator as userid,operatedate as operatedate from  workflow_requestlog where requestid="+requestid +" and destnodeid<>nodeid and logtype in('0') and nodeid=(\n" +
                "select nodeid from workflow_flownode wfn left join workflow_nodebase wn on wfn.nodeid=wn.id \n" +
                "where workflowid=(select workflowid from (\n" +
                "select rownum  as  rm,workflowid from "+table+" fm left join workflow_requestbase wr \n" +
                "on fm.requestid = wr.requestid where rownum =1 )) and (wn.nodename like'%"+nodeName+"%')) order by LOGID desc ) where rm=1";
        log.writeLog(nodeSql);
        RecordSet rs = new RecordSet();
        rs.execute(nodeSql);
        if (rs.next()) {
            Currentoperator = rs.getString("userid");
        }
        return Currentoperator;
    }

    /**
     * 根据workflowid获取tablename
     * @param wfid
     * @return
     */
    public String getTableNameByWfid(String wfid){
        RecordSet rs = new RecordSet();
        rs.execute("select tablename from workflow_base left join workflow_bill \n" +
                "on workflow_base.formid=workflow_bill.id where workflow_base.id="+wfid);
        rs.next();
        return rs.getString("tablename");
    }
}
